﻿/*
 * Class Description            
 *                                 
 *                              
 * 
 * 
 * 
 */ 

using System;
using System.Collections.Generic;
using System.Linq;
using System.Web;
using System.Data.SqlClient;
using System.Data;
using System.DirectoryServices;


/// <summary>
/// Description for DBDriver
/// </summary>
public sealed class DBDriver
{
    //DBDriver class variables
    private SqlConnection sqlConn;
    private SqlCommand cmd;
    public SqlDataReader reader;
    private static volatile DBDriver instance; //singleton instance
    private static object syncRoot = new Object();
    private SqlDataAdapter dAdapter = new SqlDataAdapter();

    /// <summary>
    /// DBDriver Class constructor
    /// </summary>
    private DBDriver() { } 

    /// <summary>
    /// 
    /// </summary>
    public static DBDriver GetInstance
    {
        get
        {
            if (instance == null)
            {
                lock (syncRoot)
                {
                    if (instance == null)
                        instance = new DBDriver();
                }
            }
            return instance;
        }
    }


    /// <summary>
    /// 
    /// </summary>
    public void CreateSqlConnection()
    {
        if(sqlConn == null) 
        {
            //string conStr = "Server=SWAGGA-XPS-PC\\SQLENTERPRISE; Integrated Security=SSPI; Database=inDB; persist security info=true;";
            string conStr = "Server=IT-KENVIS\\SQLEXPRESS; Integrated Security=SSPI; Database=inDB; persist security info=true;";
            //string conStr = "Server=seaweed.arvixe.com;Database=inDB;Uid=demoman;Pwd=Password123;";
            sqlConn = new SqlConnection(conStr);
            sqlConn.Open();  
        }
    }


    /// <summary>
    ///
    /// </summary>
    public /*int*/ List<String> Authenticate(String el, String pw)
    {
        List<String> ls = new List<string>();

        //cmd = new SqlCommand("SELECT [pid], [ework], [passw] FROM inDB.dbo.person_table WHERE [ework]='" + el + "' AND PWDCOMPARE('" + pw + "',[passw])=1", sqlConn);
        //cmd = new SqlCommand("SELECT [pid], [group] FROM inDB.dbo.person_table WHERE [ework]='" + el + "' AND PWDCOMPARE('" + pw + "',[passw])=1", sqlConn);
        cmd = new SqlCommand("SELECT inDB.dbo.person_table.pid, inDB.dbo.usergroups.groups FROM inDB.dbo.person_table " +
                             "JOIN inDB.dbo.usergroups " +
                             "ON inDB.dbo.person_table.groupid = inDB.dbo.usergroups.id " +
                             "WHERE [ework]='" + el + "' AND PWDCOMPARE('" + pw + "',[passw])=1", sqlConn);

        reader = cmd.ExecuteReader();
        if (reader.HasRows)
        {
            reader.Read();

            ls.Add(reader[0].ToString());
            ls.Add(reader[1].ToString());
            reader.Close();

            return ls;
            //i = (int)reader[0];
            //reader.Close();
            //return i;
        }
        else
        {
            reader.Close();
            return null;
        }
    }


    /// <summary>
    /// 
    /// </summary>
    /// <param name="SqlString"></param>
    public void GodInsert (String SqlString)
    {
        //CreateSqlConnection();
        cmd = new SqlCommand(SqlString, sqlConn);
        cmd.ExecuteNonQuery();
    }
    /// <summary>
    /// 
    /// </summary>
    /// <param name="SqlString"></param>
    /// <returns></returns>
    public int GodInsertWithNewID(String SqlString)
    {
        //CreateSqlConnection();
        SqlString += "; Select Scope_Identity()";
        cmd = new SqlCommand(SqlString, sqlConn);
        int x = Convert.ToInt32(cmd.ExecuteScalar());
        //cmd.ExecuteScalar();
        return x;
    }
    /// <summary>
    /// 
    /// </summary>
    /// <param name="SqlString"></param>
    /// <param name="Table_fld"></param>
    /// <returns></returns>
    public List<int> GetIndexList( String SqlString, String Table_fld )
    {
        //CreateSqlConnection();
        List<int> indexlist = new List<int>();

        cmd = new SqlCommand(SqlString, sqlConn);
        reader = cmd.ExecuteReader();
        if (reader.HasRows)
        {
            while (reader.Read())
                indexlist.Add((int)reader[Table_fld]);
            reader.Close();
            return indexlist;
        }
        else
        {
            reader.Close();
            return null;
        }
    }


    /// <summary>
    /// 
    /// </summary>
    /// <param name="SqlString"></param>
    /// <returns></returns>
    public SqlDataReader GodReader( String SqlString )
    {
        //CreateSqlConnection();

        cmd = new SqlCommand(SqlString, sqlConn);
        reader = cmd.ExecuteReader();
        if (reader.HasRows)
        {
            return reader;
        }
        else
        {
            reader.Close();
            return null;
        }
    }


    /// <summary>
    /// 
    /// </summary>
    /// <param name="loc"></param>
    /// <param name="quan"></param>
    public void SpecialUpdate (String loc, String quan)
    {
        //CreateSqlConnection();

        SqlCommand cmd = new SqlCommand("spAddLocation", sqlConn);

        cmd.CommandType = CommandType.StoredProcedure;

        // 3. add parameter to command, which 
        //    will be passed to the stored procedure 
        cmd.Parameters.Add(new SqlParameter("@Quantity", quan));
        cmd.Parameters.Add(new SqlParameter("@Location", loc));

        // execute the command 
        reader = cmd.ExecuteReader();

        // iterate through results, printing each to console 
        //while (reader.Read())
        //{
        //    Console.WriteLine(
        //        "Product: {0,-35} Total: {1,2}",
        //        reader["location"],
        //        reader["quantity"]);
        //}
        reader.Close();
    }



    /// <summary>
    /// 
    /// </summary>
    /// <param name="SqlString"></param>
    /// <param name="i"></param>
    /// <returns></returns>
    public List<String> GetList(String SqlString, int i)
    {
        int x = 0;
        List<String> sl = new List<String>(); 

        cmd = new SqlCommand(SqlString, sqlConn);
        reader = cmd.ExecuteReader();
        reader.Read();
        while (x < i)
        {
            sl.Add(reader[x].ToString());
            x++;
        }     
        reader.Close();
        return sl;
    }
    

    /// <summary>
    /// 
    /// </summary>
    /// <param name="SqlString"></param>
    /// <returns></returns>
    public String GetString(String SqlString, int i)
    {
        //CreateSqlConnection();
        String s;
        cmd = new SqlCommand(SqlString, sqlConn);
        reader = cmd.ExecuteReader();
        if (reader.HasRows)
        {
            reader.Read();
            switch (i) {
                case 1:
                    s = Convert.ToString(reader[0]);
                    reader.Close();
                    return s;
                case 3: 
                    s = Convert.ToString(reader[0]) + " " + Convert.ToString(reader[1]) + " " + Convert.ToString(reader[2]).Substring(0,1);
                    reader.Close();
                    return s;
                
                default:
                    reader.Close();
                    return "";
            }
        }
        else
        {
            reader.Close();
            return "";
        }
    }


    /// <summary>
    /// 
    /// </summary>
    /// <returns></returns>
    public SqlConnection GetCon()
    {
        return sqlConn;
    } 

    public SqlDataAdapter GetAdp()
    {
        return dAdapter;
    }
    /// <summary>
    /// 
    /// </summary>
    public void CloseSqlConnection()
    {
        this.sqlConn.Close();
        if (reader != null)
        {
            this.reader.Close();
            reader = null;
        }
        instance = null;
    }
}